using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Reflection;

namespace RikMigrations.Providers
{
	public class MssqlProvider : DbProvider
	{
		public MssqlProvider( string filename ) : base(filename) {}

		#region Basic Commands
		protected override DbConnection CreateNewConnection()
		{
			DbConnection retVal;
			retVal = new SqlConnection(ConnectionString);
			retVal.Open();
			return retVal;
		}

		protected override DbParameter CreateParameter(int i, object value)
		{
			return new SqlParameter("@" + i, value);
		}
		#endregion

		#region Introspection

        public override IntrospectionCommand<bool> GetTableExistsCommand(string tableName)
        {
            return new MssqlTableExistsCommand(new Table(this, tableName, false));
        }
		#endregion

	    public override int GetModuleVersion(string moduleName)
		{
			InitialiseDb();
			object o = ExecuteScalar("SELECT CurrentVersion FROM ModuleVersions WHERE ModuleName = @0", moduleName);
			return o == null ? 0 : (int)o;
		}

	    public override void SetModuleVersion(string moduleName, int version)
		{
			InitialiseDb();
			object o = ExecuteScalar("SELECT CurrentVersion FROM ModuleVersions WHERE ModuleName = @0", moduleName);
			if (o == null)
				ExecuteNonQuery("INSERT INTO ModuleVersions (ModuleName, CurrentVersion) VALUES (@0, @1)", moduleName, version);
			else
				ExecuteNonQuery("UPDATE ModuleVersions SET CurrentVersion = @0 WHERE ModuleName = @1", version, moduleName);
		}

		#region Commands
        protected internal override Command GetCreateTableCommand(Table table)
		{
			return new MssqlCreateTableCommand(table);
		}

        protected internal override Command GetCreateViewCommand(View view)
        {
            return new MssqlCreateViewCommand(view);
        }

        protected internal override Command GetAddColumnCommand(Column column)
		{
			return new MssqlAddColumnCommand(column);
		}

        protected internal override Command GetDropColumnCommand(Table table, string columnName)
		{
			return new MssqlDropColumnCommand(table,  columnName);
		}

        protected internal override Command GetCreateSPCommand(StoredProcedure sp)
        {
            return new MssqlCreateStoredProcedureCommand(sp);
        }

        protected internal override Command GetInsertCommand(string tableName, object objectToInsert, bool IsIdentityInsert)
        {
            return new MssqlInsertDataCommand(tableName, objectToInsert, IsIdentityInsert);
	    }

        protected internal override Command GetChangeDataTypeCommand(string tableName, string columnName, Type newColumnType, int length)
	    {
            return new MssqlChangeDataTypeCommand(tableName, columnName, newColumnType, length);
	    }

	    #region Table Commands

		class MssqlCreateTableCommand : Command
		{
			Table table;

			public MssqlCreateTableCommand(Table table)
			{
				this.table = table;
			}

			public override void Execute(DbProvider provider)
			{
				try
				{
					MssqlProvider msDb = provider as MssqlProvider;
					string sql = string.Format("CREATE TABLE [{1}].[{0}] (", table.Name, provider.SchemaName ?? "dbo");
					foreach (Column col in table.Columns)
						sql += msDb.GetColumnSql(provider, col) + ",\r\n";
					foreach (Constraint constraint in table.Constraints)
						sql += GetConstraintSql(provider, constraint) + ",\r\n";
					sql = sql.Substring(0, sql.Length - 3);
					sql += ")";
					provider.ExecuteNonQuery(sql);
					Console.WriteLine( "Created New Table ('{0}')", table.Name );
				}
				catch (Exception e)
				{
					Console.WriteLine( "Error Creating New Table ('{0}')", table.Name );
					throw new Exception("Error creating table: " + table.Name + "\n" + e.Message + "\n" + e.StackTrace , e);
				}
			}
		}

        class MssqlDropTableCommand : Command {
		    private readonly Table table;

            public MssqlDropTableCommand(Table table)
			{
                this.table = table;
			}

			public override void Execute(DbProvider provider)
			{
				try
				{
					Console.WriteLine( "Dropped Table ('{0}')", table.Name );
					provider.ExecuteNonQuery(string.Format("DROP TABLE [{1}].[{0}]", table.Name, provider.SchemaName ?? "dbo"));
				}
				catch (Exception e)
				{
					Console.WriteLine( "Error Dropping Table ('{0}')", table.Name );
					throw new Exception("Error dropping table: " + table.Name + "\n" + e.Message + "\n" + e.StackTrace , e);
				}
			}
        }
		#endregion

        #region Insert Command

        class MssqlInsertDataCommand : Command
        {
            private readonly string _tableName;
            private readonly object _objectToInsert;
            private readonly bool _isIdentityInsert;

            public MssqlInsertDataCommand(string tableName, object objectToInsert, bool isIdentityInsert)
            {
                _tableName = tableName;
                _objectToInsert = objectToInsert;
                _isIdentityInsert = isIdentityInsert;
            }

            public override void Execute(DbProvider provider)
            {
                if (_isIdentityInsert)
                    provider.ExecuteNonQuery(String.Format("SET IDENTITY_INSERT [{0}] On", _tableName));

                provider.ExecuteNonQuery(CreateInsertCommandString(_tableName, provider.SchemaName, _objectToInsert));

                if (_isIdentityInsert)
                    provider.ExecuteNonQuery(String.Format("SET IDENTITY_INSERT [{0}] Off", _tableName));
            }

            private string CreateInsertCommandString(string tableName, string schemaName, object objectToInsert)
            {
                string columnNames = "";
                string values = "";
                foreach (PropertyInfo property in objectToInsert.GetType().GetProperties())
                {
                    object val = property.GetValue(objectToInsert, null);
                    if (val is int)
                        values += val + ",";
                    else if (val is DBNull)
                        values += "NULL,";
                    else
                        values += String.Format("'{0}',", val);
                    columnNames += "[" + property.Name + "],";
                }

                return String.Format("INSERT INTO [{3}].[{0}] ({1}) VALUES ({2})", tableName, columnNames.TrimEnd((',')), values.TrimEnd((',')), schemaName ?? "dbo");
            } 
        }
	    #endregion

        #region View Commands
        class MssqlCreateViewCommand : Command
        {
            private View view;

            public MssqlCreateViewCommand(View view)
            {
                this.view = view;
            }

            public override void Execute(DbProvider provider)
            {
                try
                {
                    // check if scripted or custom generated
                    if (!String.IsNullOrEmpty(view.Script))
                        provider.ExecuteScalar(String.Format("Create view [{2}].[{0}] as {1}", view.Name, view.Script, provider.SchemaName ?? "dbo"));
                    else 
                        Log.WriteLine("Skipping creation of View: {0}", view.Name);
                } catch (Exception e)
                {
                    throw new Exception("Error creating view: " + view.Name + "\r\n" + String.Format("Create view dbo.{0} as {1}", view.Name, view.Script) + "\r\n", e);
                }
            }
        }

        class MssqlDropViewCommand : Command {
		    private readonly View view;

            public MssqlDropViewCommand(View view)
			{
                this.view = view;
			}

			public override void Execute(DbProvider provider)
			{
				try
				{

                    provider.ExecuteNonQuery(string.Format("DROP VIEW [{1}].[{0}]", view.Name, provider.SchemaName ?? "dbo"));
				}
				catch (Exception e)
				{
					throw new Exception("Error dropping view: " + view.Name + "\n" + e.Message + "\n" + e.StackTrace , e);
				}
			}
        }
        #endregion

        #region Column Commands
        internal class MssqlAddColumnCommand : Command
		{
			Column column;

			public MssqlAddColumnCommand(Column column)
			{
				this.column = column;
			}

			public override void Execute(DbProvider provider)
			{
				MssqlProvider msDb = provider as MssqlProvider;
				string sql = msDb.GetColumnSql(provider, column);
				provider.ExecuteNonQuery(string.Format("ALTER TABLE [{1}].[{0}] ADD {2}", column.Table.Name, provider.SchemaName ?? "dbo", sql));
			}
		}

        internal class MssqlCreateStoredProcedureCommand : Command
        {
            private StoredProcedure sp;

            public MssqlCreateStoredProcedureCommand(StoredProcedure sp)
            {
                this.sp = sp;
            }

            public override void Execute(DbProvider provider)
            {
                try
                {
                    // check if scripted or custom generated
                    if (!String.IsNullOrEmpty(sp.Script))
                        provider.ExecuteNonQuery(sp.Script);
                    else
                        Log.WriteLine("Skipping creation of Stored Procedure: {0}", sp.Name);
                }
                catch (Exception e)
                {
                    throw new Exception("Error creating Stored Procedure: " + sp.Name, e);
                }
            }
        }

	    internal class MssqlDropColumnCommand : Command
		{
			Table table;
			string name;

			public MssqlDropColumnCommand(Table table, string name)
			{
				this.table = table;
				this.name = name;
			}

			public override void Execute(DbProvider provider)
			{
				DropAllConstraints(provider);
				provider.ExecuteNonQuery(string.Format("ALTER TABLE [{1}].[{0}] DROP COLUMN {2}", table.Name, provider.SchemaName ?? "dbo", name));
			}

			void DropDefaultConstraints(DbProvider db)
			{
				List<string> constraints = new List<string>();
				using (DbDataReader reader = db.ExecuteReader(@"SELECT name FROM sys.default_constraints AS a
									WHERE (OBJECT_NAME(parent_object_id) = @0) AND (parent_column_id = (SELECT column_id FROM sys.columns WHERE (object_id = a.parent_object_id) AND (name = @1)))",
												table.Name, name))
				{
					while (reader.Read())
						constraints.Add(reader.GetString(0));
				}
				DropConstraints(db, constraints);
			}

			void DropAllConstraints(DbProvider db)
			{
				List<string> other = new List<string>();
				List<string> primary = new List<string>();
				using (DbDataReader reader = db.ExecuteReader(@"SELECT a.CONSTRAINT_NAME, CONSTRAINT_TYPE
					FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
					INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
					WHERE a.TABLE_NAME = @0 AND COLUMN_NAME = @1", table.Name, name))
				{
					while (reader.Read())
					{
						switch (reader.GetString(1))
						{
							case "PRIMARY KEY":
								primary.Add(reader.GetString(0));
								break;
							default:
								other.Add(reader.GetString(0));
								break;
						}
					}
				}
				DropConstraints(db, other);
				DropConstraints(db, primary);
				DropDefaultConstraints(db);
			}

			void DropConstraints(DbProvider db, List<string> constraintNames)
			{
				foreach (string constraint in constraintNames)
				{
					Log.WriteLine("Dropping {0}", constraint);
					db.ExecuteNonQuery(string.Format("ALTER TABLE [{1}].[{0}] DROP CONSTRAINT {2}", table.Name, db.SchemaName ?? "dbo", constraint));
				}
			}
		}

        public class MssqlChangeDataTypeCommand : Command
        {
            private readonly string tableName;
            private readonly string columnName;
            private readonly Type type;
            private readonly int length;

            public MssqlChangeDataTypeCommand(string tableName, string columnName, Type type, int length)
            {
                this.tableName = tableName;
                this.columnName = columnName;
                this.type = type;
                this.length = length;
            }

            public override void Execute(DbProvider provider)
            {
                provider.ExecuteNonQuery(string.Format("ALTER TABLE [{1}].[{0}] ALTER COLUMN [{2}] {3}", tableName, provider.SchemaName ?? "dbo", columnName, GetSqlType(type, length, false, false)));
            }
        }



		#endregion

		#region Foreign Key Commands
        protected internal override Command GetAddForeignKeyCommand(ForeignKey key)
		{
			return new CommandBase(delegate(DbProvider db) {
				db.ExecuteNonQuery(
					string.Format("ALTER TABLE [{1}].[{0}] ADD CONSTRAINT [{2}] FOREIGN KEY ({3}) REFERENCES [{5}].[{4}]([{6}]) ON DELETE {7} ON UPDATE {8}",
					key.Table.Name, db.SchemaName ?? "dbo", key.Name, key.Column, key.ForeignTable, key.ForeignSchema, key.ForeignColumn, GetCascadeSql(key.DeleteCascadeOption), GetCascadeSql(key.UpdateCascadeOption)));
			});
		}

		protected internal override Command GetAddCompoundForeignKeyCommand( CompoundForeignKey key )
		{
			return new CommandBase( delegate( DbProvider db )
			{
				db.ExecuteNonQuery(
					string.Format( "ALTER TABLE [{1}].[{0}] ADD CONSTRAINT [{2}] FOREIGN KEY ({3}) REFERENCES [{5}].[{4}]({6}) ON DELETE {7} ON UPDATE {8}",
					key.Table.Name, db.SchemaName ?? "dbo", key.Name, ConcatenateColumnsIntoList( key.Columns ), key.ForeignTable, key.ForeignSchema, ConcatenateColumnsIntoList(key.ForeignColumns), GetCascadeSql( key.DeleteCascadeOption ), GetCascadeSql( key.UpdateCascadeOption ) ) );
			} );
		}

		private string ConcatenateColumnsIntoList( string[] columns )
		{
			StringBuilder sb = new StringBuilder();
			for( int i = 0; i < columns.Length; i++ )
			{
				sb.Append( "[" );
				sb.Append(columns[i]);
				sb.Append( "]" );
				if( i + 1 < columns.Length )
					sb.Append( ", " );
			}
			return sb.ToString();
		}

		string GetCascadeSql(CascadeOptions options)
		{
			switch (options)
			{
				case CascadeOptions.NoAction:
					return "NO ACTION";
				case CascadeOptions.Cascade:
					return "CASCADE";
				case CascadeOptions.SetNull:
					return "SET NULL";
				case CascadeOptions.SetDefault:
					return "SET DEFAULT";
				default:
					throw new ArgumentOutOfRangeException();
			}
		}
		#endregion

		#region Column Commands
        protected internal override Command GetRenameTableCommand(Column column, string newName)
		{
			return new CommandBase(delegate(DbProvider db) {
				db.ExecuteNonQuery(
					string.Format("sp_rename '[{1}].[{0}].[{2}]', '{3}', 'COLUMN'", column.Table.Name, db.SchemaName ?? "dbo", column.Name, newName));
			});
		}
		#endregion

		#region Schema Commands

		class MssqlAddSchemaCommand : Command
		{
			public override void Execute(DbProvider provider)
			{
				if(string.IsNullOrEmpty(provider.SchemaName) || provider.SchemaName == "dbo")
					throw new InvalidOperationException();
				Console.WriteLine("Added Schema: {0}", provider.SchemaName);
				provider.ExecuteNonQuery(string.Format("CREATE SCHEMA [{0}]", provider.SchemaName));
			}
		}

		class MssqlDropSchemaCommand : Command
		{
			public override void Execute(DbProvider provider)
			{
				if (string.IsNullOrEmpty(provider.SchemaName) || provider.SchemaName == "dbo")
					throw new InvalidOperationException();
				Console.WriteLine("Dropped Schema: {0}", provider.SchemaName);
				provider.ExecuteNonQuery(string.Format("DROP SCHEMA [{0}]", provider.SchemaName));
			}
		}

		#endregion
		#endregion

		#region Utilities

        public string GetLiteral(Type type, object value)
        {
            return base.GetLiteral(type, value);
        }

		public void DeleteAllTables()
		{
			ExecuteNonQuery(@"WHILE EXISTS(SELECT [name] FROM sys.tables WHERE [type] = 'U')
				BEGIN
				DECLARE @table_name varchar(50)
				DECLARE table_cursor CURSOR FOR SELECT [name] FROM sys.tables WHERE [type] = 'U'
				OPEN table_cursor
				FETCH NEXT FROM table_cursor INTO @table_name
				WHILE @@FETCH_STATUS = 0
				BEGIN
				BEGIN TRY
				EXEC ('DROP TABLE [' + @table_name + ']')
				PRINT 'Dropped Table ' + @table_name
				END TRY
				BEGIN CATCH END CATCH
				FETCH NEXT FROM table_cursor INTO @table_name
				END
				CLOSE table_cursor
				DEALLOCATE table_cursor
				END");
		}

		public static string GetSqlType(Type type, int length, bool isBlob, bool isUnicode)
		{
			if (type == typeof(bool))
				return "bit";
			else if (type == typeof(Guid))
				return "uniqueidentifier";
			else if (type == typeof(string))
			{
				string prefix = "";
				if (isUnicode)
					prefix = "n";
				if (isBlob)
					return prefix + "text";
				if (length > 8000 || (isUnicode && length > 4000))
					return prefix + "varchar(max)";
				else
					return prefix + "varchar(" + length + ")";
			}
			else if (type == typeof(char))
			{
				if (isUnicode)
					return "nchar";
				else
					return "char";
			}
			else if (type == typeof(byte))
				return "tinyint";
			else if (type == typeof(short))
				return "smallint";
			else if (type == typeof(int))
				return "int";
			else if (type == typeof(long))
				return "bigint";
			else if (type == typeof(double))
				return "float";
			else if (type == typeof(float))
				return "real";
			else if (type == typeof(DateTime))
				return "datetime";
			else if (type == typeof(decimal))
				return "decimal"; // TODO: Add support for precision and scale
			else if (type == typeof(byte[]))
			{
				if (isBlob)
					return "image";
				else
				{
					if (length > 8000)
						return "varbinary(max)";
					else
						return "varbinary(" + length + ")";
				}
			}
			else
				throw new NotImplementedException(string.Format("RikMigrations currently has no mapping of '{0}' for MS-SQL", type));
		}

		public static Type GetClrType(string sqlType)
		{
			switch (sqlType.ToLower())
			{
				case "tinyint":
					return typeof(byte);
				case "smallint":
					return typeof(short);
				case "int":
					return typeof(int);
				case "bigint":
					return typeof(long);

				case "float":
					return typeof(double);
				case "real":
					return typeof(float);

				case "smallmoney":
				case "money":
				case "numeric":
				case "decimal":
					return typeof(decimal);

				case "bit":
					return typeof(bool);
				case "uniqueidentifier":
					return typeof(Guid);

				case "varchar":
				case "nvarchar":
				case "text":
				case "ntext":
					return typeof(string);

				case "char":
				case "nchar":
					return typeof(char);

				case "smalldatetime":
				case "datetime":
					return typeof(DateTime);

				case "varbinary":
				case "image":
					return typeof(byte[]);

				default:
					throw new NotImplementedException(string.Format("RikMigrations currently has no mapping of '{0}' for MS-SQL", sqlType));
			}
		}

		public string GetColumnSql(DbProvider provider, Column column)
		{
			string sql = string.Format("[{0}] {1}", column.Name, column.SqlType ?? GetSqlType(column.Type, column.Length, column.IsBlob, column.IsUnicode));
			if (column.IsPrimaryKey)
				sql += " PRIMARY KEY";
			if (!column.AllowNull)
				sql += " NOT NULL";
			if (column.IsUnique)
				sql += " UNIQUE";
			if (column.DefaultValue != null)
				sql += " DEFAULT(" + GetLiteral(column.Type, column.DefaultValue) + ")";
			if (column.IsAutoGenerated)
				sql += " IDENTITY(1,1)";
			if (column.ForeignKey != null)
				sql += " REFERENCES [" + (column.ForeignKey.Schema ?? provider.SchemaName ?? "dbo") + "].[" + column.ForeignKey.Table + "](" + column.ForeignKey.Column + ")";
			if (column.ExtraOptions != null)
				sql += " " + column.ExtraOptions;
			return sql;
		}

		public static string GetConstraintSql(DbProvider provider, Constraint constraint)
		{
			string sql;
			switch (constraint.Type)
			{
				case ConstraintType.PrimaryKey:
					sql = string.Format("CONSTRAINT [{0}] PRIMARY KEY (", constraint.Name);
					break;
				case ConstraintType.Unique:
					sql = string.Format("CONSTRAINT [{0}] UNIQUE (", constraint.Name);
					break;
				default:
					throw new NotImplementedException();
			}
			foreach (string col in constraint.Columns)
				sql += col + ",";
			return sql.Substring(0, sql.Length - 1) + ")";
		}
		#endregion

		protected internal override Command GetAddSchemaCommand()
		{
			return new MssqlAddSchemaCommand();
		}

		protected internal override Command GetDropSchemaCommand()
		{
			return new MssqlDropSchemaCommand();
		}

		protected internal override Command GetDropTableCommand(string tableName)
	    {
	        return new MssqlDropTableCommand(new Table(this, tableName, false));
	    }

	    protected internal override Command GetDropViewCommand(string viewName)
	    {
            return new MssqlDropViewCommand(new View(this, viewName, false, ""));
	    }

	    public override IntrospectionCommand<bool> GetColumnExists(string tableName, string columnName)
	    {
	        return new MssqlColumnExistsCommand(tableName, columnName);
	    }

	    public override IntrospectionCommand<List<ForeignKey>> GetFindForeignKeys(Table tableName)
	    {
            return new MssqlFindForeignKeysCommand(tableName);
	    }

	    public override IntrospectionCommand<bool> GetStoredProcedureExists(string name)
	    {
	        return new MssqlGetStoredProcedureExistsCommand(name);
	    }

	    public override IntrospectionCommand<bool> GetViewExistsCommand(string viewName)
	    {
	        return new MssqlViewExistsCommand(viewName);
	    }
	}

    internal class MssqlViewExistsCommand : IntrospectionCommand<bool>
    {
        private readonly string viewName;

        public MssqlViewExistsCommand(string viewName)
        {
            this.viewName = viewName;
        }

        public override void Execute(DbProvider provider)
        {
            Result = provider.ExecuteScalar("SELECT 1 FROM sysobjects WHERE xtype='v' AND name=@0", viewName) != null;
        }
    }

    internal class MssqlGetStoredProcedureExistsCommand : IntrospectionCommand<bool>
    {
        private readonly string storedProcedureName;

        public MssqlGetStoredProcedureExistsCommand(string storedProcedureName)
        {
            this.storedProcedureName = storedProcedureName;
        }

        public override void Execute(DbProvider provider)
        {
            Result = provider.ExecuteScalar("SELECT 1 FROM information_schema.routines where routine_name = @0", storedProcedureName) != null;
        }
    }

    internal class MssqlFindForeignKeysCommand : IntrospectionCommand<List<ForeignKey>>
    {
        private readonly Table table;

        public MssqlFindForeignKeysCommand(Table table)
        {
            this.table = table;
        }

        public override void Execute(DbProvider provider)
        {
            List<ForeignKey> retVal = new List<ForeignKey>();
            using (var reader = provider.ExecuteReader(@"SELECT
                                                    CONSTRAINT_NAME = FK.CONSTRAINT_NAME,
                                                    COLUMN_NAME = FK_COLS.COLUMN_NAME,
                                                    REFERENCED_TABLE_NAME = PK.TABLE_NAME,
                                                    REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
                                                    REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
                                                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
                                                    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
                                                    ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
                                                    AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
                                                    AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
                                                    AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
                                                    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
                                                    AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
                                                    AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
                                                    AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
                                                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
                                                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME
                                                    WHERE FK.TABLE_NAME = @0", table.Name)) {
                while (reader.Read())
                    retVal.Add(new ForeignKey(reader.GetString(0), table, reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4)));
            }
            Result = retVal;
        }
    }

    internal class MssqlColumnExistsCommand : IntrospectionCommand<bool>
    {
        private readonly string table;
        private readonly string name;

        public MssqlColumnExistsCommand(string table, string name)
        {
            this.table = table;
            this.name = name;
        }

        public override void Execute(DbProvider provider)
        {
            Result = provider.ExecuteScalar("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @0 AND COLUMN_NAME = @1", table, name) != null;
        }
    }

    internal class MssqlTableExistsCommand : IntrospectionCommand<bool>
    {
        private Table table;

        public MssqlTableExistsCommand(Table table)
        {
            this.table = table;
        }

        public override void Execute(DbProvider provider)
        {
            Result = provider.ExecuteScalar("SELECT 1 FROM sysobjects WHERE xtype='u' AND name=@0", table.Name) != null;
        }
    }

    public abstract class IntrospectionCommand<T> : Command
    {
        public T Result { get; set; }
    }
}
